<?php
/**
 * @category 	BoardDao.php
 * @author 		Sung Hoon, Cho (akdddnet@gmail.com)
 * @since		2011. 7. 5.
 * @license		BSD License
 * @version		0.1  
 * 
 */
 
global $CFG;
require_once $CFG->FW_DIR."/mvc/Dao.php";
 
class BoardDao extends Dao {
	
 	function __construct(array $db_type = null) {
 		parent::__construct($db_type);	
 	}
 	
 	
 	function bbs(DTO $dto) {
 	
 		$query = "
 				SELECT 
 							b.*
 							,(SELECT COUNT(*) FROM board_comment bc WHERE bc.b_seq = b.b_seq) AS comment_cnt
 							,bc.bc_name
 				FROM 		board b
 				LEFT JOIN	board_category bc ON bc.bc_seq = b.bc_seq AND ba_seq = :ba_seq
 				WHERE		1 = 1
 				AND			ba_id 		= :ba_id
 				AND			b_ddate IS NULL
 				
 		";
 		
 		$bind = array(
 				"ba_id"=>$dto->eb->ba_id
 				,"ba_seq"=>$dto->eb->ba_seq
 		);
 		
 		
 		// 카테고리 분류인 경우.
 		if(!Common::isNullOrEmpty($dto->eb->bc_seq)) {
 			$query = "
 				AND			bc_seq = :bc_seq
 			";
 			$bind["bc_seq"] = $dto->eb->bc_seq;
 		}
 		
 		// 검색 관련
 		$this->getSearchQueryAdd($query, $bind, $dto); 
 		
 		
 		// 정렬
 		$query .= " ORDER BY b_seq DESC";
 		
 		
 		// 페이징
 		$query .= " 
 			LIMIT ".$dto->eb->offset.", ".$dto->eb->limit;
 		
 		$dto->list = $this->db->query($query, $bind)->fetchAll();
 		
 		return $dto;
 	}
 	
 	
 	function view(DTO $dto) {
 		$query = "
 					SELECT 		b.*, bc.bc_name
 					FROM		board b
 					LEFT JOIN	board_category bc ON bc.bc_seq = b.bc_seq
 					WHERE		1 = 1
 					AND			b.ba_id = :ba_id
 					AND			b_seq = :b_seq
 					AND			b_ddate IS NULL
 		";
 		
 		$bind = array(
 					 "ba_id"=>$dto->eb->ba_id
 					,"b_seq"=>$dto->eb->b_seq
 		);
 		
 		$dto->obj = $this->db->query($query, $bind)->fetch();
 		
 		return $dto;
 	}
 	
 	
 	
 	
 	
 	
 	
	function getBoardInfo(DTO $dto) {
 		$query ="
 				SELECT		*
 				FROM 		board_admin
 				WHERE		1 = 1
 				AND			ba_id = :ba_id
 				AND			ba_ddate IS NULL
 		";
 		
 		$bind = array(
 				"ba_id"=>$dto->eb->ba_id
 		);
 		
 		$dto->obj = $this->db->query($query, $bind)->fetch();
 		
 		return $dto;
 	}
 	
 	
 	function getTotalCount(DTO $dto) {
 		
 		$bind = array();
 		
 		$query = "
 				SELECT 		COUNT(*)
 				FROM		board
 				WHERE		1 = 1 
 				AND			ba_id = :ba_id
 				AND			b_ddate IS NULL
 		";
 		
 		$bind["ba_id"] = $dto->eb->ba_id;
 		
 		if(!Common::isNullOrEmpty($dto->eb->bc_seq)) {
 			$query = "
 				AND			bc_seq = :bc_seq
 			";
 			$bind["bc_seq"] = $dto->eb->bc_seq;
 		}
 		
 		
 		// 검색 관련
 		$this->getSearchQueryAdd($query, $bind, $dto);
 		
 		$dto->rc = $this->db->query($query, $bind)->field();
 		
 		return $dto;
 	}
 	
 	
 	private function getSearchQueryAdd(&$query, &$bind, &$dto) {
 		// 검색 정보가 존재할 경우.
 		if($dto->eb->issearch) {
 			$kf = $dto->eb->kf;
 		
 			switch($kf) {
 				default :
 				case 'subject' :
 					$query .= '
 		 				AND		b_subject LIKE CONCAT("%", :kw, "%")
 		 			';
 				break;
 				case 'content' :
 					$query .= '
 		 				AND		b_content LIKE CONCAT("%", :kw, "%")
 		 			';
 					break;
 				case 'all' :
 					$query .= '
 		 				AND		(
 		 						b_subject LIKE CONCAT("%", :kw, "%")
 		 						OR
 		 						b_content LIKE CONCAT("%", :kw, "%")
 		 				)
 		 			';
 					break;
 			}
 		
 			$bind['kw'] = $dto->eb->kw;
 		}
 	}
 	
 	function category(DTO $dto) {
 		$query = "
 						SELECT 
 									bc_seq, bc_name
 						FROM		board_category
 						WHERE		1 = 1
 						AND			ba_seq = :ba_seq
 						AND 		bc_ddate IS NULL
 						ORDER BY 	bc_order ASC
 		";
 		
 		$bind = array(
 						"ba_seq"=>$dto->eb->ba_seq
 		);
 		
 		
 		$dto->list = $this->db->query($query, $bind)->fetchAll();
 		
 		return $dto;
 	}
 	
 	
 	function getGeneralCategorySeq(DTO $dto) {
 		$query = "
 					SELECT 
 								bc_seq
 					FROM 		board_category
 					WHERE		1 = 1
 					AND			ba_id = :ba_id
 					AND 		bc_ddate IS NULL
 					AND 		bc_delete = 0
 		";
 		
 		$bind = array(
 						"ba_id"=>$dto->eb->ba_id
 		);
 		
 		$dto->obj = $this->db->query($query, $bind)->field();
 		
 		return $dto;
 	}
 	
 	
 	function doAdd(DTO $dto) {
 		$query = "
 				INSERT INTO board
 					(ba_id, bc_seq, m_id, m_name, b_notice, b_subject, b_content, b_ip, b_cdate)
 				VALUES
 					(:ba_id, :bc_seq, :m_id, :m_name, :b_notice, :b_subject, :b_content, :b_ip, now())
 		";
 		
 		
 		$bind = array(
 				 "ba_id"=>$dto->eb->ba_id
 				,"bc_seq"=>$dto->eb->bc_seq
 				,"m_id"=>$dto->eb->m_id
 				,"m_name"=>$dto->eb->m_name
 				,"b_notice"=>$dto->eb->b_notice
 				,"b_subject"=>$dto->eb->b_subject
 				,"b_content"=>$dto->eb->b_content
 				,"b_ip"=>$dto->eb->b_ip
 		);
 		
 		
 		$newid = 0;
 		
 		$this->db->begin();
 		if($this->db->query($query, $bind)->rowCount()) {
 			// 추가된 게시판 번호 알아오기
 			$newid = $this->db->insertId();
 			
 			
 			// board_category, board_admin 테이블에 글 수 추가하기
 			$query = "
 				UPDATE 
 										board_admin		a
							INNER JOIN 	board_category 	c 	ON c.ba_seq = a.ba_seq AND c.bc_seq = :bc_seq 
				SET
							 a.ba_count		= a.ba_count + 1
							,c.bc_count		= c.bc_count + 1
							
				WHERE		1 = 1
				AND			a.ba_seq = :ba_seq
 			";
 			
 			$bind = array(
 					"bc_seq"=>$dto->eb->bc_seq
 					,"ba_seq"=>$dto->eb->ba_seq
 			);
 			
 			$dto->rc = $this->db->query($query, $bind)->rowCount();
 			if($dto->rc > 0) {
 				$this->db->commit();
 			} else{
 				$this->db->rollback();
 			}
 		}
 		
 		$dto->rc = $newid;
 		return $dto;
 	}
 	
 	
 	function doHitAdd(DTO $dto) {
 		$query = "
 					UPDATE	board	SET
 							b_count = b_count + 1
 					WHERE	1 = 1
 					AND		b_seq = :b_seq
 		";
 		
 		$dto->rc = $this->db->query($query, array('b_seq'=>$dto->eb->b_seq))->rowCount();
 		
 		return $dto;
 	}
 	
 	
 	
 	function doModify(DTO $dto) {
 		$query = "
 					UPDATE 	
 								board 
 					SET
 								 b_subject = :b_subject
 								,b_content = :b_content 
 		";
 		
 		$bind = array(
 						 "b_subject"=>$dto->eb->b_subject
 						,"b_content"=>$dto->eb->b_content
 		);
 		
 		$category = $dto->eb->bc_seq;
 		$is_category_change = false;
 		if(!Common::isNullOrEmpty($category)) {
 			$ocategory = $dto->eb->bc_oseq;
 			
 			if($category != $ocategory) {
 				$is_category_change = true;
 				$query .= "
 								,bc_seq = :bc_seq
 				";		
 				
 				
 				$bind["bc_seq"] = $category;
 			}
 		}
 		
 		
 		
 		$query .= "
 					WHERE		1 = 1
 					AND			ba_id = :ba_id
 					AND			b_seq = :b_seq
 		";
 		
 		$bind["ba_id"] = $dto->eb->ba_id;
 		$bind["b_seq"] = $dto->eb->b_seq;
 		
 		
 		$rc = 0;
 		// 카테고리가 변경이 된경우 board_category에 count값을 변경 시켜야됨.
 		// 2개 테이블에 update해야되므로 트렌젝션이 발생되어야 함.
 		if($is_category_change) {
 			$this->db->begin();
 			
 			// 해당글이 정상적으로 Update된 경우 board_category에 글 갯수 변경시켜줘야됨.
 			if($this->db->query($query, $bind)->rowCount()) {
 				$query = "
 						UPDATE 
		 							board_category
		 				SET
		 							bc_count = bc_count + 1
		 				WHERE		1 = 1
		 				AND			ba_id = :ba_id
		 				AND			bc_seq = :bc_seq
 				";
 				
 				$bind = array(
 								 "ba_id"=>$dto->eb->ba_id
 								,"bc_seq"=>$dto->eb->bc_seq
 				);
 				
 				if($this->db->query($query, $bind)->rowCount()) {
 					$query = "
 						UPDATE 
		 							board_category
		 				SET
		 							bc_count = bc_count - 1
		 				WHERE		1 = 1
		 				AND			ba_id = :ba_id
		 				AND			bc_seq = :bc_seq
	 				";
	 				
	 				$bind = array(
	 								 "ba_id"=>$dto->eb->ba_id
	 								,"bc_seq"=>$dto->eb->bc_oseq
	 				);	
	 				
	 				$rc = $this->db->query($query, $bind)->rowCount();
	 				
	 				if($rc > 0) {
	 					$this->db->commit();	
	 				} else {
	 					$this->db->rollback();
	 				}
 				} else {
 					$this->db->rollback();
 				}
 				
 			} else {
 				$this->db->rollback();
 			}
 		} else {
 			$rc = $this->db->query($query, $bind)->rowCount();
 		}
 		
 		$dto->rc = $rc;
 		return $dto;
 	}
 	
 	
 	function doDelete(DTO $dto) {
 		$query = "
 					UPDATE		
 								board
 					SET
 								b_ddate = now()
 					WHERE		1 = 1
 					AND			ba_id = :ba_id
 					AND			b_seq = :b_seq
 		";
 		
 		$bind = array(
 						 "ba_id"=>$dto->eb->ba_id
 						,"b_seq"=>$dto->eb->b_seq
 		);
 		
 		$this->db->begin();
 		$rc = 0;
 		if($this->db->query($query, $bind)->rowCount()) {
 			// board_category count 줄이기
 			$query = "
 					UPDATE		
 								board_category
 					SET
 								bc_count = bc_count - 1
 					WHERE		1 = 1
 					AND			ba_id = :ba_id
 					AND			bc_seq = :bc_seq
 			";
 			
 			$bind = array( 
 					 "ba_id"=>$dto->eb->ba_id
 					,"bc_seq"=>$dto->eb->bc_seq
 			);
 			
 			if($this->db->query($query, $bind)->rowCount()) {	
 				$query ="
 						UPDATE	
 									board_admin
 						SET
 									ba_count = ba_count -1
 						WHERE		1 = 1
 						AND			ba_id = :ba_id
 				";
 				
 				$bind = array(
 					"ba_id"=>$dto->eb->ba_id
 				);
 				
 				
 				if($rc = $this->db->query($query, $bind)->rowCount()) {
 					$this->db->commit();
 				}  else {
 					$this->db->rollback();
 				}
 			} else{
 				$this->db->rollback();
 			}
 			
 		} else {
 			$this->db->rollback();
 		}
 		
 		
 		$dto->rc = $rc;
 		
 		return $dto;
 	}
 	
 	
 	
 	function getAttach(DTO $dto) {
 		$query = "
 		 	 				SELECT		*
 		 	 				FROM		attach
 		 	 				WHERE		1 = 1
 		 	 				AND			a_table = :a_table
 		 	 				AND			a_key = :a_key 
 		 	 		";
 	
 		$bind = array(
 		 	'a_table'=>'board'
 		,'a_key'=>$dto->eb->b_seq
 		);
 	
 		$dto->list = $this->db->query($query, $bind)->fetchAll();
 	
 		return $dto;
 	}
 	
 	
 	function doFileMove(DTO $dto) {
 		$query = "
 	 	 			UPDATE attach SET
 	 						a_table = :a_table
 	 						,a_key = :a_key
 	 						,a_path = :a_path
 	 						,a_comple = :a_comple
 	 				WHERE	1 = 1
 	 				AND		a_seq = :a_seq
 	 	 		";
 	
 		$bind = array(
 	 	 				'a_table'=>$dto->eb->a_table
 		,'a_key'=>$dto->eb->a_key
 		,'a_path'=>$dto->eb->a_path
 		,'a_comple'=>$dto->eb->a_comple
 		,'a_seq'=>$dto->eb->a_seq
 		);
 	
 		$dto->rc = $this->db->query($query, $bind)->rowCount();
 	
 		return $dto;
 	}
 	
 	function doAttachDelete(DTO $dto) {
 		$query = "
 	 					DELETE FROM attach
 	 					WHERE	1 = 1
 	 					AND		a_seq IN (".$dto->eb->seq.")
 	 		";
 			
 		$dto->rc = $this->db->query($query);
 			
 		return $dto;
 	}
 	
 	
 	function getTempAttach(DTO $dto) {
 		$query = "
 	 					SELECT
 	 								*
 	 					FROM		attach
 	 					WHERE		1 = 1
 	 					AND			a_seq IN (".$dto->eb->seq.")
 	 		";
 			
 		$dto->list = $this->db->query($query)->fetchAll();
 			
 		return $dto;
 	}
}